﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

using MapCenterSystem.DataInfo;
using System.Data.SqlClient;
using MapCenterSystem.Configuration;
using MapCenterSystem.Utility;

namespace MapCenterSystem.DataAccess
{
    public class PlaceTypeServiceEx: PlaceServices
    {
        public PlaceTypeInfo GetPlaceTypeByDescription(string des)
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_PlaceType_GetPlaceTypeByDescription";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                command.Parameters.AddWithValue("@des", des);
                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                SqlDataReader reader = command.ExecuteReader();
                PlaceTypeInfo placeTypeInfo = new PlaceTypeInfo();
                while (reader.Read())
                {
                    placeTypeInfo.ID = Convert.ToInt32(reader["ID"]);
                    placeTypeInfo.Description = Convert.ToString(reader["description"]);
                    placeTypeInfo.Picture = Convert.ToString(reader["picture"]);
                }
                return placeTypeInfo;
            }
        }

        public int AddPlaceType(PlaceTypeInfo type)
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_PlaceType_AddPlaceType";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                command.Parameters.AddWithValue("@description", type.Description);

                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                try
                {
                    return Convert.ToInt32(command.ExecuteNonQuery());
                }
                catch (SqlException ex)
                {
                    return -1;
                }
            }
        }

        public bool UpdatePlaceType(PlaceTypeInfo type)
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_PlaceType_UpdatePlaceType";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                command.Parameters.AddWithValue("@id", type.ID);
                command.Parameters.AddWithValue("@description", type.Description);
                
                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    return false;
                }
                return true;
            }
        }

        public bool UpdatePlaceTypePicture(PlaceTypeInfo type)
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_PlaceType_UpdatePicture";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                command.Parameters.AddWithValue("@id", type.ID);
                command.Parameters.AddWithValue("@picture", type.Picture);

                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    return false;
                }
                return true;
            }
        }

        public bool DeletePlaceType(int id)
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_PlaceType_DeletePlaceType";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                command.Parameters.AddWithValue("@id", id);
                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    return false;
                }
                return true;
            }
        }

        public IList<PlaceTypeInfo> GetListPlaceType()
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_PlaceType_GetListPlaceType";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                SqlDataReader reader = command.ExecuteReader();
                IList<PlaceTypeInfo> result = new List<PlaceTypeInfo>();
                
                while (reader.Read())
                {
                    PlaceTypeInfo placeTypeInfo = new PlaceTypeInfo();
                    placeTypeInfo.ID = Convert.ToInt32(reader["ID"]);
                    placeTypeInfo.Description = Convert.ToString(reader["description"]);
                    placeTypeInfo.Picture = Convert.ToString(reader["picture"]);
                    result.Add(placeTypeInfo);
                }
                return result;
            }
        }


    }
}

